﻿using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EFDemo
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (MyContext dc = new MyContext())
            {
                ///创建数据库、初始化数据
                InitData(dc);
                Console.WriteLine();



                var students = dc.Set<Student>().AsNoTracking().ToList();
                Console.WriteLine("1. 简单查询  AsNoTracking不启用本地跟踪");
                Console.WriteLine();


                var students2 = dc.Set<Student>().Include(x => x.StudentMajor).ThenInclude(x => x.Major).ToList();
                Console.WriteLine("2. 简单查询   使用导航属性联表，多级联表");
                Console.WriteLine();


                var student3 = dc.Set<Student>().Select(x => new
                {
                    name = x.Name,
                    majors = string.Join(',', x.StudentMajor.Select(y => y.Major.MajorCode))
                }).ToList();
                Console.WriteLine("3. 对查询字段进行简单处理，比如将多行数据转为一行逗号分隔的数据");
                Console.WriteLine();


                var student4 = dc.Set<Student>().Where(x => x.StudentMajor.Select(y => y.Major.School.SchoolName).Contains("School1")).ToList();
                Console.WriteLine("4. 简单查询   在联表中使用查询条件");
                Console.WriteLine();


                //var student5 = dc.Set<Student>().GroupBy(x => x.IsValid).Select(x => new
                var student5 = dc.Students.GroupBy(x => x.IsValid)
                    .Select(x => new
                    {
                        name = x.Key,
                        count = x.Count(),
                        dto = x.Select(y=>y.Age)
                    }).ToList();
                Console.WriteLine("分组查询");
                Console.WriteLine();


                var student6 = dc.Set<Student>().GroupBy(x => new { x.IsValid, x.EnRollDate }, x => x.Age).Where(x => x.Average() > 15).Select(x => new
                {
                    name = x.Key.IsValid,
                    date = x.Key.EnRollDate,
                    count = x.Count(),
                    aveAge = x.Average()
                }).ToList();
                Console.WriteLine("**********************复杂查询 ********分组查询 having");
                Console.WriteLine();


                var student7 = dc.Set<Student>().SelectMany(x => x.StudentMajor, (a, b) => new
                {
                    stu = a,
                    school = b.Major.School.SchoolName
                }).GroupBy(x => new { x.school, x.stu.EnRollDate }, x => x.stu.Age).Where(x => x.Average() > 15).Select(x => new
                {
                    school = x.Key.school,
                    date = x.Key.EnRollDate,
                    count = x.Count(),
                    aveAge = x.Average()
                }).ToList();
                Console.WriteLine("**********************复杂查询 ******** 集合选择");
                Console.WriteLine();


                var studentsNoKey = dc.Set<Student2>().ToList();
                Console.WriteLine("无集合外键 主键");
                Console.WriteLine();

                //var studentsNoKey2 = dc.Set<Student2>().Include(x => x.StudentMajor).ThenInclude(x => x.Major).ToList(); //不能使用Include
                var studentsNoKey3 = dc.Set<Student2>().Select(x => new
                {
                    name = x.Name,
                    majors = string.Join(',', dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name).Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major }).Select(y => y.maj.MajorCode))
                }).ToList();

                var studentsNoKey4 = dc.Set<Student2>().Where(x =>
                            dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name)
                            .Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major })
                            .Join(dc.Set<School2>(), y => y.maj.SchoolName, y => y.SchoolName, (last, school) => new { last = last, school = school })
                            .Select(y => y.school.SchoolName).Contains("School1")
               ).ToList();

                var studentsNoKey5 = dc.Set<Student2>().GroupBy(x => x.IsValid).Select(x => new
                {
                    name = x.Key,
                    count = x.Count()
                }).ToList();

                var studentsNoKey6 = dc.Set<Student2>().GroupBy(x => new { x.IsValid, x.EnRollDate }, x => x.Age).Where(x => x.Average() > 15).Select(x => new
                {
                    name = x.Key.IsValid,
                    date = x.Key.EnRollDate,
                    count = x.Count(),
                    aveAge = x.Average()
                }).ToList();

                var studentsNoKey7 = dc.Set<Student2>().SelectMany(x =>
                                    dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name).Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major })
                                    .Join(dc.Set<School2>(), y => y.maj.SchoolName, y => y.SchoolName, (last, school) => new { last = last, school = school })
                                    .Select(y => y.school.SchoolName),
                            (a, b) => new
                            {
                                stu = a,
                                school = b
                            }).GroupBy(x => new { x.school, x.stu.EnRollDate }, x => x.stu.Age).Where(x => x.Average() > 15).Select(x => new
                            {
                                school = x.Key.school,
                                date = x.Key.EnRollDate,
                                count = x.Count(),
                                aveAge = x.Average()
                            }).ToList();

            }
            Console.WriteLine("Hello World!");
        }

       public static void InitData(MyContext dc)
        {
            //如果是第一次建库
            if (dc.Database.EnsureCreated() == true)
            {
                //初始化有主外键关系的表
                List<School> schools = new List<School>();
                for (int i = 1; i <= 10; i++)
                {
                    School s = new School
                    {
                        SchoolCode = "s00" + i,
                        SchoolName = "School" + i,
                        Majors = new List<Major>()
                    };
                    for (int j = 1; j <= 5; j++)
                    {
                        Major m = new Major
                        {
                            MajorCode = "m00" + j,
                            MajorName = $"School{i}_Major{j}"
                        };
                        s.Majors.Add(m);
                    }
                    schools.Add(s);
                }
                Random r = new Random();
                List<Student> students = new List<Student>();
                for (int i = 1; i <= 100; i++)
                {
                    Student stu = new Student
                    {
                        Name = "Student" + i,
                        IsValid = i % 3 == 0,
                        EnRollDate = DateTime.Today.AddDays(i % 5),
                        Age = r.Next(10, 20)
                    };
                    students.Add(stu);
                }

                List<StudentMajor> middle = new List<StudentMajor>();

                for (int i = 1; i <= 1000; i++)
                {
                    StudentMajor m = new StudentMajor
                    {
                        Major = schools[r.Next(10)].Majors[r.Next(5)],
                        Student = students[r.Next(100)]
                    };
                    middle.Add(m);
                }
                dc.Schools.AddRange(schools);
                dc.Students.AddRange(students);
                dc.Set<StudentMajor>().AddRange(middle);
                dc.SaveChanges();

                //初始化没有主外键关联的表
                List<School2> schools2 = new List<School2>();
                List<Major2> majors2 = new List<Major2>();
                for (int i = 1; i <= 10; i++)
                {
                    School2 s = new School2
                    {
                        SchoolCode = "s00" + i,
                        SchoolName = "School" + i
                    };
                    for (int j = 1; j <= 5; j++)
                    {
                        Major2 m = new Major2
                        {
                            MajorCode = "m00" + j,
                            MajorName = $"School{i}_Major{j}",
                            SchoolName = s.SchoolName
                        };
                        majors2.Add(m);
                    }
                    schools2.Add(s);
                }
                List<Student2> students2 = new List<Student2>();
                for (int i = 1; i <= 100; i++)
                {
                    Student2 stu = new Student2
                    {
                        Name = "Student" + i,
                        IsValid = i % 3 == 0,
                        EnRollDate = DateTime.Today.AddDays(i % 5),
                        Age = r.Next(10, 20)
                    };
                    students2.Add(stu);
                }

                List<StudentMajor2> middle2 = new List<StudentMajor2>();

                for (int i = 1; i <= 1000; i++)
                {
                    StudentMajor2 m = new StudentMajor2
                    {
                        MajorName = majors2[r.Next(50)].MajorName,
                        StudentName = students2[r.Next(100)].Name
                    };
                    middle2.Add(m);
                }
                dc.SchoolsNoFK.AddRange(schools2);
                dc.MajorsNoFK.AddRange(majors2);
                dc.StudentsNoFK.AddRange(students2);
                dc.Set<StudentMajor2>().AddRange(middle2);
                dc.SaveChanges();

            }
        }

    }
}
